# -*- encoding: utf-8 -*-
"""
封装连接数据库及数据库操作方法
"""

from sshtunnel import SSHTunnelForwarder
import pymysql
from dbutils.pooled_db import PooledDB
import time
from Common.log import MyLog

global connect_list
global connect_datebase_list
connect_datebase_list = []
connect_list = []


class SQL:
    """
    封装连接数据库和sql操作方法
    """
    global connect_list
    global connect_datebase_list

    def __init__(self):
        self.log = MyLog

    def connect_database(self, db_info):
        """
        连接数据库基础方法
        :param db_info: 数据库基础信息
        :return
        """
        global connect_list
        if db_info['databaseName'] not in connect_datebase_list:
            """
            封装连接数据库的方法，支持通过跳板机连接和直接连接，支持连接mysql数据库和postgresql数据库
            """

            # db_type = db_info['databaseType']
            host = db_info['host']
            port = int(db_info['port'])
            user = db_info['user']
            psw = db_info['password']
            stepping = db_info['isJump']

            # 判断是否需要登录跳板机
            if stepping == 'yes':
                server_host = db_info['jumpHost']
                server_port = int(db_info['jumpPort'])
                server_name = db_info['jumpName']
                ssh_key = 'Config/id_rsa'
                # 定义开始的时间
                start_time = time.time()
                server = SSHTunnelForwarder(
                    # 跳板机ip与ssh登录端口号
                    ssh_address_or_host=(server_host, server_port),
                    # 跳板机登录账号
                    ssh_username=server_name,
                    # PC(客户端)的私钥路径
                    ssh_pkey=ssh_key,
                    # 远程MYSQL服务器的绑定的IP和端口号
                    remote_bind_address=(host, port)
                )
                server.start()
                host = '127.0.0.1'
                port = server.local_bind_port
                # 定义结束时间
                stop_time = time.time()
                self.log.info(
                    '---------Operation to complete !  connect-JumpServer-time: ' + str(
                        round((stop_time - start_time), 3)) + ' S')

            # 定义开始的时间
            start_time1 = time.time()
            db_name = db_info['databaseName']
            # 使用数据库连接池的方式连接数据库
            pool = PooledDB(creator=pymysql,
                            mincached=2,  # mincached，最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接
                            maxcached=10,  # maxcached，最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接
                            maxshared=10,  # 最大的共享连接数,当连接数达到这个数，新请求的连接会分享已经分配出去的连接
                            maxconnections=10,  # 最大允许连接数据
                            blocking=True,
                            # 当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，直到当前连接数小于最大连接数，如果这个值是False，会报错
                            host=host,
                            port=port,
                            database=db_name,
                            user=user,
                            password=psw)
            conn = pool.connection()
            # 定义结束时间
            stop_time1 = time.time()
            self.log.info(
                '---------Operation to complete !  connect-db-time: ' + str(
                    round((stop_time1 - start_time1), 3)) + ' S')
            connect_datebase_list.append(db_info['databaseName'])
            connect_list.append(conn)
            return conn
        else:
            conn_index = connect_datebase_list.index(db_info['databaseName'])
            return connect_list[conn_index]
